-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, 2012 and Azure
-- --------------------------------------------------
-- Date Created: 04/10/2014 12:46:52
-- Generated from EDMX file: c:\users\dickbutt\documents\visual studio 2013\Projects\EntityFramework_Laboration3\EFDAL\WebsiteModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [EntityFramework_Laboration3];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_AccountWorkAds]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[WorkAdsSets] DROP CONSTRAINT [FK_AccountWorkAds];
GO
IF OBJECT_ID(N'[dbo].[FK_CVJob]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[JobEntrySets] DROP CONSTRAINT [FK_CVJob];
GO
IF OBJECT_ID(N'[dbo].[FK_AccountWorkAds_Account]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AccountWorkAds] DROP CONSTRAINT [FK_AccountWorkAds_Account];
GO
IF OBJECT_ID(N'[dbo].[FK_AccountWorkAds_WorkAds]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AccountWorkAds] DROP CONSTRAINT [FK_AccountWorkAds_WorkAds];
GO
IF OBJECT_ID(N'[dbo].[FK_AccountContactInformation]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ContactInformationSets] DROP CONSTRAINT [FK_AccountContactInformation];
GO
IF OBJECT_ID(N'[dbo].[FK_MeritCV_Merit]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[MeritCV] DROP CONSTRAINT [FK_MeritCV_Merit];
GO
IF OBJECT_ID(N'[dbo].[FK_MeritCV_CV]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[MeritCV] DROP CONSTRAINT [FK_MeritCV_CV];
GO
IF OBJECT_ID(N'[dbo].[FK_AccountCV]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CVSets] DROP CONSTRAINT [FK_AccountCV];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[AccountSets]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AccountSets];
GO
IF OBJECT_ID(N'[dbo].[ContactInformationSets]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ContactInformationSets];
GO
IF OBJECT_ID(N'[dbo].[CVSets]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CVSets];
GO
IF OBJECT_ID(N'[dbo].[JobEntrySets]', 'U') IS NOT NULL
    DROP TABLE [dbo].[JobEntrySets];
GO
IF OBJECT_ID(N'[dbo].[MeritSets]', 'U') IS NOT NULL
    DROP TABLE [dbo].[MeritSets];
GO
IF OBJECT_ID(N'[dbo].[WorkAdsSets]', 'U') IS NOT NULL
    DROP TABLE [dbo].[WorkAdsSets];
GO
IF OBJECT_ID(N'[dbo].[AccountWorkAds]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AccountWorkAds];
GO
IF OBJECT_ID(N'[dbo].[MeritCV]', 'U') IS NOT NULL
    DROP TABLE [dbo].[MeritCV];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'AccountSets'
CREATE TABLE [dbo].[AccountSets] (
    [AccountId] int IDENTITY(1,1) NOT NULL,
    [Authority] int  NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [Password] nvarchar(max)  NOT NULL,
    [Deleted] bit  NOT NULL
);
GO

-- Creating table 'ContactInformationSets'
CREATE TABLE [dbo].[ContactInformationSets] (
    [ContactInformationId] int IDENTITY(1,1) NOT NULL,
    [CompleteName] nvarchar(max)  NOT NULL,
    [PhoneNumber] nvarchar(max)  NOT NULL,
    [Account_AccountId] int  NOT NULL
);
GO

-- Creating table 'CVSets'
CREATE TABLE [dbo].[CVSets] (
    [CVId] int IDENTITY(1,1) NOT NULL,
    [CVText] nvarchar(max)  NOT NULL,
    [Account_AccountId] int  NOT NULL
);
GO

-- Creating table 'JobEntrySets'
CREATE TABLE [dbo].[JobEntrySets] (
    [JobId] int IDENTITY(1,1) NOT NULL,
    [StartDate] datetime  NOT NULL,
    [EndDate] datetime  NULL,
    [Deleted] bit  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [CVId] int  NOT NULL
);
GO

-- Creating table 'MeritSets'
CREATE TABLE [dbo].[MeritSets] (
    [MeritId] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Level] int  NOT NULL
);
GO

-- Creating table 'WorkAdsSets'
CREATE TABLE [dbo].[WorkAdsSets] (
    [WorkId] int IDENTITY(1,1) NOT NULL,
    [EnteredDate] datetime  NOT NULL,
    [ExpiredDate] datetime  NOT NULL,
    [Deleted] bit  NOT NULL,
    [Filled] bit  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [AccountId] int  NOT NULL
);
GO

-- Creating table 'AccountWorkAds'
CREATE TABLE [dbo].[AccountWorkAds] (
    [Applicants_AccountId] int  NOT NULL,
    [AppliedJob_WorkId] int  NOT NULL
);
GO

-- Creating table 'MeritCV'
CREATE TABLE [dbo].[MeritCV] (
    [Merit_MeritId] int  NOT NULL,
    [CV_CVId] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [AccountId] in table 'AccountSets'
ALTER TABLE [dbo].[AccountSets]
ADD CONSTRAINT [PK_AccountSets]
    PRIMARY KEY CLUSTERED ([AccountId] ASC);
GO

-- Creating primary key on [ContactInformationId] in table 'ContactInformationSets'
ALTER TABLE [dbo].[ContactInformationSets]
ADD CONSTRAINT [PK_ContactInformationSets]
    PRIMARY KEY CLUSTERED ([ContactInformationId] ASC);
GO

-- Creating primary key on [CVId] in table 'CVSets'
ALTER TABLE [dbo].[CVSets]
ADD CONSTRAINT [PK_CVSets]
    PRIMARY KEY CLUSTERED ([CVId] ASC);
GO

-- Creating primary key on [JobId] in table 'JobEntrySets'
ALTER TABLE [dbo].[JobEntrySets]
ADD CONSTRAINT [PK_JobEntrySets]
    PRIMARY KEY CLUSTERED ([JobId] ASC);
GO

-- Creating primary key on [MeritId] in table 'MeritSets'
ALTER TABLE [dbo].[MeritSets]
ADD CONSTRAINT [PK_MeritSets]
    PRIMARY KEY CLUSTERED ([MeritId] ASC);
GO

-- Creating primary key on [WorkId] in table 'WorkAdsSets'
ALTER TABLE [dbo].[WorkAdsSets]
ADD CONSTRAINT [PK_WorkAdsSets]
    PRIMARY KEY CLUSTERED ([WorkId] ASC);
GO

-- Creating primary key on [Applicants_AccountId], [AppliedJob_WorkId] in table 'AccountWorkAds'
ALTER TABLE [dbo].[AccountWorkAds]
ADD CONSTRAINT [PK_AccountWorkAds]
    PRIMARY KEY CLUSTERED ([Applicants_AccountId], [AppliedJob_WorkId] ASC);
GO

-- Creating primary key on [Merit_MeritId], [CV_CVId] in table 'MeritCV'
ALTER TABLE [dbo].[MeritCV]
ADD CONSTRAINT [PK_MeritCV]
    PRIMARY KEY CLUSTERED ([Merit_MeritId], [CV_CVId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [AccountId] in table 'WorkAdsSets'
ALTER TABLE [dbo].[WorkAdsSets]
ADD CONSTRAINT [FK_AccountWorkAds]
    FOREIGN KEY ([AccountId])
    REFERENCES [dbo].[AccountSets]
        ([AccountId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AccountWorkAds'
CREATE INDEX [IX_FK_AccountWorkAds]
ON [dbo].[WorkAdsSets]
    ([AccountId]);
GO

-- Creating foreign key on [CVId] in table 'JobEntrySets'
ALTER TABLE [dbo].[JobEntrySets]
ADD CONSTRAINT [FK_CVJob]
    FOREIGN KEY ([CVId])
    REFERENCES [dbo].[CVSets]
        ([CVId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CVJob'
CREATE INDEX [IX_FK_CVJob]
ON [dbo].[JobEntrySets]
    ([CVId]);
GO

-- Creating foreign key on [Applicants_AccountId] in table 'AccountWorkAds'
ALTER TABLE [dbo].[AccountWorkAds]
ADD CONSTRAINT [FK_AccountWorkAds_Account]
    FOREIGN KEY ([Applicants_AccountId])
    REFERENCES [dbo].[AccountSets]
        ([AccountId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [AppliedJob_WorkId] in table 'AccountWorkAds'
ALTER TABLE [dbo].[AccountWorkAds]
ADD CONSTRAINT [FK_AccountWorkAds_WorkAds]
    FOREIGN KEY ([AppliedJob_WorkId])
    REFERENCES [dbo].[WorkAdsSets]
        ([WorkId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AccountWorkAds_WorkAds'
CREATE INDEX [IX_FK_AccountWorkAds_WorkAds]
ON [dbo].[AccountWorkAds]
    ([AppliedJob_WorkId]);
GO

-- Creating foreign key on [Account_AccountId] in table 'ContactInformationSets'
ALTER TABLE [dbo].[ContactInformationSets]
ADD CONSTRAINT [FK_AccountContactInformation]
    FOREIGN KEY ([Account_AccountId])
    REFERENCES [dbo].[AccountSets]
        ([AccountId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AccountContactInformation'
CREATE INDEX [IX_FK_AccountContactInformation]
ON [dbo].[ContactInformationSets]
    ([Account_AccountId]);
GO

-- Creating foreign key on [Merit_MeritId] in table 'MeritCV'
ALTER TABLE [dbo].[MeritCV]
ADD CONSTRAINT [FK_MeritCV_Merit]
    FOREIGN KEY ([Merit_MeritId])
    REFERENCES [dbo].[MeritSets]
        ([MeritId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [CV_CVId] in table 'MeritCV'
ALTER TABLE [dbo].[MeritCV]
ADD CONSTRAINT [FK_MeritCV_CV]
    FOREIGN KEY ([CV_CVId])
    REFERENCES [dbo].[CVSets]
        ([CVId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MeritCV_CV'
CREATE INDEX [IX_FK_MeritCV_CV]
ON [dbo].[MeritCV]
    ([CV_CVId]);
GO

-- Creating foreign key on [Account_AccountId] in table 'CVSets'
ALTER TABLE [dbo].[CVSets]
ADD CONSTRAINT [FK_AccountCV]
    FOREIGN KEY ([Account_AccountId])
    REFERENCES [dbo].[AccountSets]
        ([AccountId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AccountCV'
CREATE INDEX [IX_FK_AccountCV]
ON [dbo].[CVSets]
    ([Account_AccountId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------